package net.bd;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.classes.SearchedWord;
import net.wfsim.loader.BancoDeDados;
import net.wfsim.log.StatusDisplay;
import net.wfsim.structures.form.Form;
import net.wfsim.structures.form.FormElement;

import java.sql.PreparedStatement;

public class MySQL {
	
	private static Statement statement;

	private static String truncateTables = "delete from @table"; 
	
	private static String preBaseTable = "select id,concat('**',label,'**',value,'**') as label\n"+
							  "  from form_element f,\n" +
							  "       cluster c,\n" +
							  "       value v\n"+
							  " where f.id = c.centroid" +
							  "   and f.id = v.element";
	
	private static String preSearchTable = "select id,concat('**',ifnull(trim(label),''),'**',ifnull(trim(value),''),'**') as label\n" +
										   "  from form_element f\n" +
										   "    left join value v\n" +
										   "      on (f.id = v.element)\n" +
										   " where f.url = ? ";
	
	private static String baseSize = "select count(distinct t_id, v_id) size\n"+
							  "  from baseSVA"; 
	
	private static String searchSize = "select count(distinct t_id, v_id) size\n"+
			  						   "  from searchSVA";
	
	private static String svaTable = "insert into @tableSVA(t_id, v_id ,token)\n"+
			  				  "values ( ? , ? , ? )";
	
	private static String TFTable = "insert into @tableTF(t_id, v_id , token, tf)\n"+
							  "select t.t_id, t.v_id, t.token, COUNT(1)\n"+
							  "  from @tableSVA t\n"+
							  " group by t_id, v_id, token";
	
	private static String IDFTable = "insert into @tableIDF(token,idf)\n"+
							  "select t.token, LOG(s.size) - LOG(COUNT(distinct t_id, v_id))\n"+
							  "  from @tableTF t,@tableSize s\n"+
							  " group by token";
	
	private static String LenTable = "insert into @tableLen(t_id, v_id ,length)\n"+
 							  "select t.t_id, t.v_id, SQRT(SUM(i.idf*i.idf*t.tf*t.tf))\n"+
							  "  from @tableTF t,@tableIDF i\n"+
							  " where t.token = i.token\n"+
							  " group by t.t_id, t.v_id";
	
	private static String WeiTable = "insert into @tableWei(t_id, v_id ,token,weight)\n"+
							  "select t.t_id, t.v_id, t.token, ifnull(t.tf*i.idf/l.length,0)\n"+
							  "  from @tableTF t,@tableIDF i,@tableLen l\n"+
							  " where t.token = i.token\n"+
							  "   and t.t_id = l.t_id\n" +
							  "   and t.v_id = l.v_id";
	
	private static String SizeTable = "insert into @tableSize(size)\n"+
							  "values ( ? )";
							
	private static String svaTableDML = "create table if not exists @tableSVA\n"+
							  "(t_id int not null,\n" +
							  " v_id int not null,\n" +
							  " token varchar(6) not null)";
	
	private static String TFTableDML = "create table if not exists @tableTF\n"+
							  "(t_id int not null,\n" +
							  " v_id int not null,\n" +
							  " token varchar(6) not null,\n" +
							  " tf int not null)";
	
	private static String IDFTableDML = "create table if not exists @tableIDF\n"+
							  "(token varchar(6) not null,\n" +
							  " idf int not null)";
	
	private static String LenTableDML = "create table if not exists @tableLen\n"+
			 				  "(t_id int not null,\n" +
			 				  " v_id int not null,\n" +
							  " length double not null)";
		
	private static String WeiTableDML = "create table if not exists @tableWei\n"+
							  "(t_id int not null,\n" +
							  " v_id int not null,\n" +
							  " token varchar(6) not null,\n" +
							  " weight double not null)";
				
	private static String SizeTableDML = "create table if not exists @tableSize\n"+
							  "(size int not null)";
	
	private static String insertIntoWFSim = "insert into form_element ( id , url , type , label )\n" +
			 				 " values ( ? , ? , ? , ? )";
	
	private static String insertFormValues = "insert into value\n" +
			 				 " value ( ? , ? )";
	
	private static String selectFormElementSeq = "select max(id) curr\n" +
						    "  from form_element";
	
	private static int ngramSize = 3;
	
	public static void inicializaBD(){
		Connection connection = ConexaoMySQL.getConexaoMySQL();
		try {
			statement = connection.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void reinicializaBD(){
		ConexaoMySQL.FecharConexao();
		Connection connection = ConexaoMySQL.getConexaoMySQL();
		try {
			statement = connection.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Statement getStatement(){
		return MySQL.statement;
	}
	
	public static void setNGramSize(int size){
		ngramSize = size;
	}
	
	public static void createDmlSchema(String s){
		
		String vSql;
		PreparedStatement S;
		try {
			
			vSql = svaTableDML.replace("@table", s);
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			S.executeUpdate();
			
			vSql = TFTableDML.replace("@table", s);
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			S.executeUpdate();
			
			vSql = IDFTableDML.replace("@table", s);
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			S.executeUpdate();
			
			vSql = LenTableDML.replace("@table", s);
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			S.executeUpdate();
			
			vSql = WeiTableDML.replace("@table", s);
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			S.executeUpdate();
			
			vSql = SizeTableDML.replace("@table", s);
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			S.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public static void populateBaseTables(){
		
		String table = "Base";
		String url = "";
		
		populateSvaTables(url, table);
		populateTables(table);
		
	}
	
	public static void populateSearchTables(String url){
		
		String table = "Search";
		
		populateSvaTables(url, table);
		populateTables(table);
		
	}
	
	public static void populateSvaTables(String url, String table){
	
		String vSqlSVA;
		String vPreSql;
		String RepairTable = "REPAIR TABLE @tableSVA QUICK";
		String vRepairTable;
		
		if (table.equals("Search")){
			vPreSql = preSearchTable;
		} else if (table.equals("Base")){
			vPreSql = preBaseTable;
		} else {
			return;
		}
		
		vSqlSVA  = svaTable.replace("@table", table);
		vRepairTable = RepairTable.replace("@table", table);
		
		int t_id;
		String label;
		SearchedWord sw = new SearchedWord();
		ResultSet result;
		PreparedStatement S;
		
		try {
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vPreSql);
			
			if (table.equals("Search")){
				S.setString(1, url);
			}
			
			result = S.executeQuery();
			
			/**
			 * populate table SVA
			 */
			while(result.next()) {
				
				t_id = Integer.parseInt(result.getString("id"));
				label = result.getString("label");
				sw.setWord(label,ngramSize);
				
				for (String tk : sw.getTokens()) {
									
					S = (PreparedStatement) statement.getConnection().prepareStatement(vSqlSVA);
					S.setInt(1, t_id);
					S.setString(2, tk);
					S.executeUpdate();
			
				}
			}	
		
			S = (PreparedStatement) statement.getConnection().prepareStatement(vRepairTable);
			S.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void populateTables(String table){
		
		String vPreSizeTable;
		String RepairTable = "REPAIR TABLE @table QUICK";
		String vRepairTable;
		
		String vSqlSearchTF = TFTable.replace("@table", table);
		String vSqlSize = SizeTable.replace("@table", table);
		String vSqlSearchIDF = IDFTable.replace("@table", table);
		String vSqlSearchLen = LenTable.replace("@table", table);
		String vSqlSearchWei = WeiTable.replace("@table", table);
		
		if (table.equals("Search")){
			vPreSizeTable = searchSize;
		} else if (table.equals("Base")){
			vPreSizeTable = baseSize;
		} else {
			return;
		}
		
		int size;
		ResultSet result;
		PreparedStatement S;
		
		try {
			
			/**
			 * populate table TF
			 */			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSqlSearchTF);
			S.executeUpdate();
			
			vRepairTable = RepairTable.replace("@table", table+"tf");
			S = (PreparedStatement) statement.getConnection().prepareStatement(vRepairTable);
			S.executeUpdate();
			
			/**
			 * populate table size
			 */			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vPreSizeTable);
			result = S.executeQuery();
			result.next();
			size = Integer.parseInt(result.getString("size"));
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSqlSize);
			S.setInt(1, size);
			S.executeUpdate();
			
			/**
			 * populate table size
			 */	
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSqlSearchIDF);
			S.executeUpdate();
			
			vRepairTable = RepairTable.replace("@table", table+"idf");
			S = (PreparedStatement) statement.getConnection().prepareStatement(vRepairTable);
			S.executeUpdate();
			
			/**
			 * populate table size
			 */	
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSqlSearchLen);
			S.executeUpdate();
			
			vRepairTable = RepairTable.replace("@table", table+"len");
			S = (PreparedStatement) statement.getConnection().prepareStatement(vRepairTable);
			S.executeUpdate();
			
			/**
			 * populate table size
			 */	
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSqlSearchWei);
			S.executeUpdate();
			
			vRepairTable = RepairTable.replace("@table", table+"wei");
			S = (PreparedStatement) statement.getConnection().prepareStatement(vRepairTable);
			S.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static int getCurrentFormId(){
		
		PreparedStatement S;
		ResultSet result = null;
		int currentId = 0;
		
		try {
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(selectFormElementSeq);
			result = S.executeQuery();
			
			if (result.next()) {
				currentId = Integer.parseInt(result.getString("curr"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return currentId;
		
	}
	
	public static void insertFormElement(int id, String url, String type, String label){
		
		PreparedStatement S;
		
		try {
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(insertIntoWFSim);
			S.setInt(1, id);
			S.setString(2, url);
			S.setString(3, type);
			S.setString(4, label);
			S.executeUpdate();
				
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void insertFormElementValues(int id, String value){
		
		PreparedStatement S;
		
		try {
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(insertFormValues);
			S.setString(1, value);
			S.setInt(2, id);
			S.executeUpdate();
				
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void associateWithCluster(String url){
		String vSql = "select f.id as s_id,\n"+
					  "	      ifnull(s.b_id,0) as b_id\n"+
					  "  from form_element f\n"+
					  "	      left join similarityjoin s\n"+
					  "         on f.id = s.s_id\n"+
					  " where f.url = ? ";
		PreparedStatement s;
		ResultSet result;
		int sId, bId;
		
		
		try {
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			s.setString(1, url);
			result = s.executeQuery();
			
			while (result.next()){
			
				bId = result.getInt("b_id");
				sId = result.getInt("s_id");
				
				if (bId > 0) {
					associateElementToCluster(sId,bId);
				} else {
					createNewCluster(sId);
				}
				
				
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	
	private static void createNewCluster(int sId) {
		
		PreparedStatement s;
		String vSqlCluster = "insert into cluster\n" +
				             "values ( ? , ? , ? )";
		
		String vSqlDeleteCluster = "delete\n" +
								   "from cluster\n"+
	                               "where centroid = ? ";
		
		String vSqlClusterToElement = "insert into cluster_to_element\n" +
	                                  "values ( ? , ? )";
		
		String vSqlDeleteClusterToElement = "delete\n" +
				   							"from cluster_to_element\n"+
				   							"where cluster = ? ";
		
		int idClusterization = 1;
		
		try {
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlDeleteClusterToElement);
			s.setInt(1, sId);
			s.executeUpdate();
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlDeleteCluster);
			s.setInt(1, sId);
			s.executeUpdate();
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlCluster);
			s.setInt(1, idClusterization);
			s.setInt(2, sId);
			s.setInt(3, 1);
			s.executeUpdate();
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlClusterToElement);
			s.setInt(1, sId);
			s.setInt(2, sId);
			s.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	private static void associateElementToCluster(int sId, int bId) {

		PreparedStatement s;
		String vSqlInsert = "insert into cluster_to_element\n" +
				            "values ( ? , ? )";
		String vSqlUpdate = "update cluster\n" +
	                        "   set modified = 1\n" +
	                        " where centroid = ?";
		
		try {
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlInsert);
			s.setInt(1, bId);
			s.setInt(2, sId);
			s.executeUpdate();
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlUpdate);
			s.setInt(1, bId);
			s.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	public static void truncateTable(String s){
		
		String vSql = truncateTables;
		PreparedStatement S;
		
		try {
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql.replace("@table", s));
			S.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static ResultSet select(PreparedStatement s){
		
		ResultSet result;
		
		try {
			
			result = s.executeQuery();
			
			return result;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
		
	}
	
	public static void dml(PreparedStatement s){
		
		try {
			
			s.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	
	public static void insertClusterization(Map<FormElement, List<FormElement>> cluster)
	{
		ArrayList<FormElement> keys = new ArrayList<FormElement>(cluster.keySet());		
		List<FormElement> allForms = new ArrayList<FormElement>();
		
		for(FormElement centroid : keys) {
			
			if(!allForms.contains(centroid)) {
				allForms.add(centroid);
			}
				
			List<FormElement> forms = cluster.get(centroid);
			for(FormElement element : forms)
			{
				if(!allForms.contains(element))
					allForms.add(element);
			}
		}
		
		System.out.println("Number of elements: "+allForms.size());
		
		for(FormElement centroid : keys) {
			
			createNewCluster(centroid.getId());
			if(!allForms.contains(centroid))
				allForms.add(centroid);
			
			List<FormElement> forms = cluster.get(centroid);
			for(FormElement element : forms)
			{
				if (element.getId() != centroid.getId())
				  associateElementToCluster(element.getId(),centroid.getId());
				if(!allForms.contains(element))
					allForms.add(element);
			}
			StatusDisplay.showStatusLoading("Inserting clusters");
		}
	}
	
	public static Form getForm(String url){
		
		PreparedStatement s;
		ResultSet r,r1;
		List<String> elementValues = new LinkedList<String>();
		List<FormElement> formElements = new LinkedList<FormElement>();
		Form f = new Form();
		
		String vSqlForm = "select id,url,type,label\n" +
		          "  from form_element\n" +
		          " where url = ? ";
		String vSqlFormElementValues = "select value\n" +
                			   "  from value v\n" +
                			   " where v.element = ? ";
		
		try {
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlForm);
			s.setString(1, url);
			r = s.executeQuery();
			
			while (r.next()){
				
				elementValues = new LinkedList<String>();
				s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlFormElementValues);
				s.setInt(1, r.getInt("id"));
				r1 = s.executeQuery();
				
				while (r1.next()){
				
					elementValues.add(r1.getString("value"));
				
				}
				
				formElements.add(new FormElement(r.getInt("id"),
												 r.getString("url"),
												 r.getString("type"),
												 r.getString("label"),
												 elementValues));
			}
			
			f.setUrl(url);
			f.setElements(formElements);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return f;
		
	}
	
	private static List<Form> getNearForms(String label){
		
		SearchedWord sw = new SearchedWord();
		int newLabelId = getCurrentFormId();
		String url = new String();
		PreparedStatement s;
		ResultSet r,r1;
		List<String> elementValues = new LinkedList<String>();
		List<FormElement> formElements = new LinkedList<FormElement>();
		List<Form> forms = new LinkedList<Form>();
		Form f = new Form();
		
		sw.setWord(label,ngramSize);
		newLabelId++;
		
		//insert into searchsva
		//populate search tables
		
		String vSqlForm = "select id,url,type,label\n" +
		          		  "  from form_element\n" +
		                  " where url = ? ";
		String vSqlFormElementValues = "select value\n" +
                			           "  from values\n" +
                			           " where element = ? ";
		
		try {
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlForm);
			s.setString(1, url);
			r = s.executeQuery();
			
			while (r.next()){
				
				elementValues = new LinkedList<String>();
				s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlFormElementValues);
				s.setInt(1, r.getInt("id"));
				r1 = s.executeQuery();
				
				while (r1.next()){
				
					elementValues.add(r1.getString("value"));
				
				}
				
				formElements.add(new FormElement(r.getInt("id"),
						 						 r.getString("url"),
												 r.getString("type"),
												 r.getString("label"),
												 elementValues));
			}
			
			f.setUrl(url);
			f.setElements(formElements);
			
			forms.add(f);
			
			f = new Form();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return forms;
		
	}
	
	public static void search(String url, String word) {
		
		PreparedStatement s;
		ResultSet r;
		int idForm = 0;
		List<Form> forms = new LinkedList<Form>();
		String vSqlHasUrl = "select ifnull(max(1),0) as hasurl\n" +
							"  from form_element\n" +
							" where url = ? \n" +
							"   and label = ? ";
		
		try {
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlHasUrl);
			s.setString(1, url);
			s.setString(2, word);
			r = s.executeQuery();
			
			while (r.next()){
				idForm = r.getInt("hasurl");
			}
			
			if (idForm > 0) {
				
				forms.add(getForm(url));
				
			} else {
				forms = getNearForms(word);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static List<Form> search(Form form) {
		
		PreparedStatement s;
		ResultSet r;
		int idForm = 0;
		String urlRetrieved;
		String url;
		List<Form> forms = new LinkedList<Form>();
		
		String vSqlHasUrl = "select ifnull(max(1),0) as hasurl\n" +
							"  from form_element\n" +
							" where url = ? ";
		
		String vSqlGetSimilarForms = "select f.url,count(1) numberofelements" +
									 "  from form_element f," +
									 "       similarityjoin s" +
									 " where f.id = s.b_id" +
									 " group by f.url";
		
		url = form.getUrl();
		
		try {
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlHasUrl);
			s.setString(1, url);
			r = s.executeQuery();
			
			while (r.next()){
				idForm = r.getInt("hasurl");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if (idForm > 0){
			forms.add(form);
		} else {
			
			for (FormElement element : form.getElements()) {
				insertFormElement(element.getId(), element.getUrl(),element.getType(), element.getLabel());
				for (String value : element.getValues()){
					insertFormElementValues(element.getId(), value);
				}			
			}
			
			truncateTable("SearchSVA");
			truncateTable("SearchTF");
			truncateTable("SearchIDF");
			truncateTable("SearchSize");
			truncateTable("SearchLEN");
			truncateTable("SearchWEI");
			populateSearchTables(url);
			
			try {
				
				s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlGetSimilarForms);
				r = s.executeQuery();
				
				while (r.next()){
					urlRetrieved = r.getString("url");
					forms.add(getForm(urlRetrieved));
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return forms;
		
		
	}
	
	public static void criaBaseTestesCampos(){
		String vSql = "insert into urls2testforms\n" +
				      "select url,\n" +
		              "       @rownum := @rownum + 1\n" +
	                  "  from (select distinct url\n" +
		              "          from form_element f\n" +
		              "         where f.domain = 'airfare'\n" +
		              "         order by rand()) f,\n" +
		              "       (select @rownum := 0) r";

		PreparedStatement s;
		
		truncateTable("urls2test");
		
		try {
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			s.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public static void criaBaseTestesForms(){
		String vSql = "insert into urls2testforms (url,domain,rownum)\n" +
				      "select url,\n" +
				      "       'airfare',\n" +
		              "       @rownum := @rownum + 1\n" +
	                  "  from (select distinct url\n" +
		              "          from form_element f\n" +
		              "         where f.domain = 'airfare'\n" +
		              "         order by rand()) f,\n" +
		              "       (select @rownum := 0) r";
		
		String vSql1 = "insert into urls2testforms (url,domain,rownum)\n" +
			      "select url,\n" +
			      "       'auto',\n" +
	              "       @rownum := @rownum + 1\n" +
                "  from (select distinct url\n" +
	              "          from form_element f\n" +
	              "         where f.domain = 'auto'\n" +
	              "         order by rand()) f,\n" +
	              "       (select @rownum := 0) r";
		
		String vSql2 = "insert into urls2testforms (url,domain,rownum)\n" +
			      "select url,\n" +
			      "       'books',\n" +
	              "       @rownum := @rownum + 1\n" +
                "  from (select distinct url\n" +
	              "          from form_element f\n" +
	              "         where f.domain = 'books'\n" +
	              "         order by rand()) f,\n" +
	              "       (select @rownum := 0) r";
		
		String vSql3 = "insert into urls2testforms (url,domain,rownum)\n" +
			      "select url,\n" +
			      "       'movie',\n" +
	              "       @rownum := @rownum + 1\n" +
                "  from (select distinct url\n" +
	              "          from form_element f\n" +
	              "         where f.domain = 'movie'\n" +
	              "         order by rand()) f,\n" +
	              "       (select @rownum := 0) r";
		
		String vSqlU = "update urls2testforms\n" +
				       "   set size = truncate(rownum / ? , 2) * 100\n" +
				       " where domain = ?";
		
		PreparedStatement s;
		
		truncateTable("urls2testforms");
		
		try {
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			s.executeUpdate();
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlU);
			s.setInt(1, 67);
			s.setString(2, "airfare");
			s.executeUpdate();
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSql1);
			s.executeUpdate();
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlU);
			s.setInt(1, 784);
			s.setString(2, "auto");
			s.executeUpdate();
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSql2);
			s.executeUpdate();
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlU);
			s.setInt(1, 161);
			s.setString(2, "books");
			s.executeUpdate();
			
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSql3);
			s.executeUpdate();
			s = (PreparedStatement) statement.getConnection().prepareStatement(vSqlU);
			s.setInt(1, 79);
			s.setString(2, "movie");
			s.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public static ArrayList getFormProbablyDomain(String s){
		
		ArrayList result = new ArrayList();
		int s_id = 0;
		int b_id = 0;
		double sim = 0.0;
		
		PreparedStatement selectStatement;
		ResultSet r;

		String vSql = "select s_id,b_id, avg(sim) as sim\n" +
					  "from (\n" +
					  "      select s.t_id as s_id,\n" +
					  "             s.v_id as sv_id,\n" +
				      "		        b.t_id as b_id,\n" +
					  "             b.v_id as bv_id,\n" +
				      "		        ifnull(sum(s.weight*b.weight),0) as sim\n" +
				      "	       from (select t_id,v_id,token,weight\n" +
				      "			       from searchwei\n" +
				      "		          where t_id in (select id\n" +
				      "							       from form_element\n" +
				      "						          where url = ? ))s\n" +
				      "		      left join basewei b\n" +
				      "	               on (s.token = b.token)\n" +
				      "	       group by s.t_id,s.v_id,b.t_id,b.v_id) x\n" +
				      "where b_id is null\n" +
				      "   or sim >= 0.5" +
				      "group by s_id,b_id\n" +
				      "order by sim desc";
		
		
		try {
			selectStatement = (PreparedStatement) statement.getConnection().prepareStatement(vSql);
			selectStatement.setString(1, s);
			r = selectStatement.executeQuery();	
			
			while (r.next()){
				s_id = r.getInt("s_id");
				b_id = r.getInt("b_id");
				sim = r.getDouble("sim");
				
				result.add(s_id);
				result.add(b_id);
				result.add(sim);
				
			}
			
			if (result == null) {
				result.add(s_id);
				result.add(b_id);
				result.add(sim);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		
		return result;
	}
	
}
